USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[TeamMember] スクリプト日付: 2014/05/07 16:55:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** オブジェクト:  UserDefinedFunction [dbo].[TeamMember]    スクリプト日付: 05/15/2014 14:41:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamMember]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TeamMember]

GO
CREATE PROCEDURE [dbo].[TeamMember]
	@TAIKAINO int,
	@TEAMNO int
AS
SELECT 
	K.KYOGISYANO, 
	K.NUMBER1, 
	K.NUMBER2, 
	K.KYOGISYAKANA + '<br>' + K.KYOGISYAMEI AS KYOGISYAMEI, 
	dbo.Seibetu(K.SEIBETU) AS SEIBETU, 
	S.SYOZOKUMEI 
FROM 
	TBL_KYOGISYA AS K, 
	TBL_TEAM AS T, 
	TBL_SYOZOKU AS S 
WHERE 
	K.TAIKAINO = @TAIKAINO 
	AND K.TAIKAINO = T.TAIKAINO 
	AND K.TAIKAINO = S.TAIKAINO 
	AND T.TEAMNO = @TEAMNO 
	AND T.KYOGISYANO = K.KYOGISYANO 
	AND K.SYOZOKUNO1 = S.SYOZOKUNO
